﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.CRM.BigCustomer;

namespace BCData.CRM.BigCustomer
{
    public class BigCustomerData : IBigCustomerData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    A.BigCustomerId,
		    A.BigCustomerGroupId,
		    A.CompanyId,
		    A.UserId,
		    A.CustomerInfoId,
		    A.BigCustomerLevel,
		    A.CreateTime,
		    A.CreateAdminId,
		    A.CreateAdminName,
		    A.UpdateTime,
		    A.EditAdminId,
		    A.EditAdminName,
		    A.IsFristAssign,
            A.EnableMoveing,
            A.EnableRunErrands,
            A.EnableSameCity,
            A.EnableSmallPieces,
            A.SameCityPrice,
            A.SmallPiecesPrice,
            A.SameCityPriceMaxWeight,
            A.SmallPiecesPriceMaxWeight,
            A.RunErrandsPrice,
            A.RunErrandsPriceMaxWeight
        FROM
            CRM_BigCustomer A";

        private readonly string sqlView = @"SELECT	
            A.BigCustomerId,
            A.BigCustomerGroupId,
		    A.CompanyId,
		    A.UserId,
		    A.CustomerInfoId,
		    A.BigCustomerLevel,
		    A.CreateTime,
		    A.CreateAdminId,
		    A.CreateAdminName,
		    A.UpdateTime,
		    A.EditAdminId,
		    A.EditAdminName,
		    A.IsFristAssign,
		    A.EnableMoveing,
		    A.EnableRunErrands,
		    A.EnableSameCity,
		    A.EnableSmallPieces,
            A.SameCityPrice,
            A.SmallPiecesPrice,
            A.SameCityPriceMaxWeight,
            A.SmallPiecesPriceMaxWeight,
            A.RunErrandsPrice,
            A.RunErrandsPriceMaxWeight,
              B.Phone,
              B.Password,
              B.UserName,
              B.CreateTime,
              B.LastLoginTime,
              B.CurrentlyLoginTime,
              B.LogonIp,
              B.AddressIp,
              B.IdentityCardNo,
              B.UserIntegrals,
              B.Avatar,
              B.AccountStatus,
              B.ProvinceId,
              B.ProvinceCode,
              B.ProvinceName,
              B.CityId,
              B.CityCode,
              B.CityName,
              B.CountyId,
              B.CountyCode,
              B.CountyName,
              B.Role,
              B.PassCode,
              B.PassExpirationTime,
              B.IsVip,
              B.IsEnterprise,
              B.CompanyName,
              B.UniqueCode,
              B.PartnerId,
              B.EvaluationScore,
              B.UserLevel,
              B.IsReceiveRedPacket,
              D.Nickname
FROM CRM_BigCustomer A
INNER JOIN USER B  ON A.UserId = B.UserId
LEFT JOIN UserAuth C ON C.UserId = B.UserId
LEFT JOIN WeChatUserInfo D ON D.Unionid=C.Unionid ";
        private readonly string sqlViewCount = @"SELECT	COUNT(1)
        FROM CRM_BigCustomer A
        INNER JOIN USER B  ON A.UserId = B.UserId
        LEFT JOIN UserAuth C ON C.UserId = B.UserId
        LEFT JOIN WeChatUserInfo D ON D.Unionid=C.Unionid ";

        #region 构造函数
        public BigCustomerData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        public async Task<BigCustomerEntity> InsertAsync(BigCustomerEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        public async Task<BigCustomerEntity> UpdateAsync(BigCustomerEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(long bigCustomerId)
        {
            string sql = "DELETE FROM crm_bigcustomer WHERE BigCustomerId = ?BigCustomerId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<BigCustomerEntity> GetEntityAsync(long bigCustomerId)
        {
            string sql = QUERY + " WHERE  A.BigCustomerId = ?BigCustomerId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerEntity>(sql, parameters);
        }
        public async Task<BigCustomerEntity> GetEntityByUserIdAsync(long userId)
        {
            string sql = QUERY + " WHERE  A.UserId = ?UserId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerEntity>(sql, parameters);
        }
        #endregion

        #region GetBigCustomerViewEntityAsync
        public async Task<BigCustomerViewEntity> GetBigCustomerViewEntityAsync(long bigCustomerId)
        {
            string sql = sqlView + " WHERE  A.BigCustomerId = ?BigCustomerId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerViewEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询大客户记录
        public async Task<(IEnumerable<BigCustomerViewEntity>, int)> GetListAsync(int? companyId, long? userId, long? bigCustomerGroupId, int? bigCustomerLevel,string userName,string provinceCode, string cityCode,string countyCode,string phone,string weChatNo, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int64);
            }

            if(userId.HasValue)
            {
                wheres.Add("A.UserId=?UserId");
                parameters.Add("?UserId", userId.Value, DbType.Int64);
            }

            if(bigCustomerGroupId.HasValue)
            {
                wheres.Add("A.BigCustomerGroupId=?BigCustomerGroupId");
                parameters.Add("?bigCustomerGroupId", bigCustomerGroupId.Value, DbType.Int64);
            }
            if(bigCustomerLevel.HasValue)
            {
                wheres.Add("A.BigCustomerLevel=?BigCustomerLevel");
                parameters.Add("?bigCustomerLevel", bigCustomerLevel.Value, DbType.Int64);
            }
            if(!string.IsNullOrWhiteSpace(userName))
            {
                wheres.Add("B.UserName  LIKE ?UserName");
                parameters.Add("?UserName", $"%{userName}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("B.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("B.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("B.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("B.Phone LIKE ?Phone");
                parameters.Add("?Phone", $"%{phone}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(weChatNo))
            {
                wheres.Add("D.Nickname LIKE ?WeChatNo");
                parameters.Add("?WeChatNo", $"%{weChatNo}%", DbType.AnsiString);
            }


            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            string sql = string.Format(@"{0} {2} LIMIT {3},{4} ; {1} {2}",
                   sqlView,
                   sqlViewCount,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<BigCustomerViewEntity>(sql, parameters);
        }
        #endregion

    }
}
//接口



